package com.duowei.diandan.constant;

import android.util.Base64;

/**
 * sql语句
 */
public class SqlStr {

    /**
     * 获取桌子列表
     */
    public static String getQueryDeskInfo() {
        String sql = clearEnterChar("SELECT CSBH,CSMC FROM JYCSSZ where ISNULL(FCSBH,'') <>''|");
        return toBase64(sql);
    }

    /**
     * 获取被占用的桌子列表
     */
    public static String getQueryUsedDeskInfo() {
        String sql = clearEnterChar("select a.CSBH, b.jysj jcsj, b.ys, b.zh, b.jcrs, b.wmdbh, b.by5 " +
                "from jycssz a, wmlsbjb b " +
                "where " +
                "  (((charindex('@' + a.csmc + ',', '@' + b.zh) > 0) or (charindex(',' + a.csmc + ',', b.zh) > 0)) and b.sfyjz <> '1' and " +
                "  b.wmdbh in (select distinct wmlsb.wmdbh " +
                "              from wmlsb)) or (((charindex('@' + a.csmc + ',', '@' + b.zh) > 0) or (charindex(',' + a.csmc + ',', b.zh) > 0)) and " +
                "  b.wmdbh in (select distinct wmdbh " +
                "              from JCZTB))|");
        return toBase64(sql);
    }

    /**
     * 结账信息表
     */
    public static String getQueryPayInfo() {
        String sql = clearEnterChar("SELECT isnull(PID,'') pid, isnull(BY1,'') by1, " +
                "isnull(BY2,'') by2, isnull(BY3,'') by3, isnull(FWQDZ,'') fwqdz, isnull(BY6,'') by6," +
                "isnull(BY7,'') by7 FROM payset|");
        return toBase64(sql);
    }


    //----------------------------------------------------------------------------------------------

    public static String clearEnterChar(String sql) {
        return sql.replaceAll("\n", "");
    }

    public static String toBase64(String str) {
        return Base64.encodeToString(str.getBytes(), Base64.DEFAULT);
    }

    public static String handleSqlText(String sql) {
        return toBase64(clearEnterChar(sql));
    }

    //----------------------------------------------------------------------------------------------
    //--------------------------------  云会员付款sql(mysql服务器)  ----------------------------------
    //----------------------------------------------------------------------------------------------


    /**
     * 用户卡信息确认SQL
     *
     * @param mobile 手机号
     * @param cardsn 卡号
     * @param weid   微信id
     */
    public static String getMemberUserCardInfo(String mobile, String cardsn, String weid) {
        String sql = "select a.from_user,a.cardsn,a.credit1,a.credit2,b.realname,b.mobile,a.status,ifnull(a.cardgrade,'云会员')cardgrade,a.active,b.occupation,a.uid " +
                "from ims_card_members a,ims_fans b " +
                "where a.weid=b.weid and a.from_user=b.from_user " +
                "and(b.mobile = '" + mobile + "' or a.cardsn = '" + cardsn + "') and a.weid=" + weid + "|";
        return sql;
    }

    /**
     * 用户券
     *
     * @param weid
     * @param fromUser
     */
    public static String getMemberTicket(String weid, String fromUser) {
        return "select a.id, a.title, a.couponmoney, sl, least_cost, cast(type as SIGNED) as type, category, discount, goods_name " +
                "from (select id, title, couponmoney, least_cost, type, category, discount, goods_name " +
                "      from ims_card_coupon" +
                "      where weid = " + weid + " and status = '1' and " +
                "            CURRENT_DATE() between CAST(FROM_UNIXTIME(starttime, '%Y-%m-%d') as datetime) and CAST( " +
                "                FROM_UNIXTIME(endtime, '%Y-%m-%d') as datetime) OR IFNULL(starttime, 0) = 0 OR IFNULL(endtime, 0) = 0) a inner join (select couponid, SUM(status) sl " +
                "                                                                               from ims_card_members_coupon " +
                "                                                                               where from_user =" +
                "                                                                                     '" + fromUser + "' and " +
                "                                                                                     weid = " + weid + " and status = 1 and ( " +
                "                                                                                       CURRENT_DATE() between CAST( " +
                "                                                                                           FROM_UNIXTIME(starttime, " +
                "                                                                                                         '%Y-%m-%d') as " +
                "                                                                                           datetime) and CAST( " +
                "                                                                                           FROM_UNIXTIME(endtime, " +
                "                                                                                                         '%Y-%m-%d') as " +
                "                                                                                           datetime) or " +
                "                                                                                       IFNULL(starttime, 0) = 0 or " +
                "                                                                                       IFNULL(endtime, 0) = 0) " +
                "                                                                               group by couponid) b " +
                "    on a.id = b.couponid|";
    }

    public static String checkNullString(String str) {
        if (str == null || str.length() == 0)
            return "";
        return str;
    }
}
